ms excel woes .. [solved]

[vhelp]

- solved - see post # 8

i am testing some excel vlookup scenarios for someone, but i seemed to have found a flaw. it has a limitation of only one column searches.

THE DATA:

id   A       B        C      D
-----------------------------------
02  123A   123A2B   123A2C  CYLI2
03  123B   123A3B   123A3C  CYLI3
04  123C   123A4B   123A4C  CYLI4
05  123D   123A5B   123A5C  CYLI5
06  123E   123A6B   123A6C  CYLI6

THE results of the VLOOKUP output:

id   A       B        C      D
-----------------------------------
02  123A   123A2B   123A2C  CYLI2
03  123B   123A3B   123A3C  CYLI3
04  123C   123A4B   123A4C  CYLI4
05  123D   123A5B   123A5C  CYLI5
06  123E   123A6B   123A6C  CYLI6

the above vlookup will search by the id column, and all matching data will fill in columns A..D

however, we need to search by other column criterias using any of the other columns, A..D, thus, if i change the vlookup search column 'id' to be column A's data as the new search criteria, so that it looks like this:

id   A       B        C      D
-----------------------------------
123A
123B
123C
123D
123E

the result fails.

is this a limitation of ms office 2000, 2003, or 2010 ? i only tested this on 2000 and 2003.

is there any other way to set up a search function that i may not know about ?

[Cornucopia]

What's your code?

Scott

[vhelp]

i messed around with the code some more, trying to figure it out, but to no avail. please note, that the actual original data file is tens of thousands of lines (rows) (or, 60k lines) and about 40 or more columns--all data. but the code snipet above is a very simplified version so that it is easy to follow, i hope. anyway.

the formula i use in the first column A2, is: =VLOOKUP(B2,data.xls!B3:I17,2,FALSE)
then, i fill-right via Ctrl+R, from column B..D to bring up the matching data from column id.

please note, i am using office 2000 here on my desktop pc, but my work pc has office 2003 and 2010. i mainly use 2003 for everything, especially ms access. the data comes from an access query, exported to excel 2003.

[vhelp]

some more details....

we crossreference a lot of files against the main data file. sometimes we need to search by another columns data as the main data to search. thus, we may search by id if we want to know how many elements we have from one clients file against our main data file. or, in other case, we may receive a clients file that we need to search by another criteria to see if any of their data is already in our main data file first, so we will search by any number of their column data. thus, columns A..D may be searched. so we will arrange the search column from the clients file to be in the first column of the vlookup. so it may be whatever column we want to search thoroughly for to make sure we don't duplicate data in our main data file.

[jman98]

Maybe you meant "woes" instead of "wows" in your subject line.

[Cornucopia]

I'm guessing you want to split your VLOOKUP() code (aka formula) into component elements of MATCH() & INDEX(). That way, the first section does the job of finding the right row(s) from the particular column in question and gives the position in the array. Then the second uses that position to give you the filtered row with all its columns intact. IOW, nest the formulas.

Scott

[vhelp]

..nest the formulas.

if you mean, all on one line, great. but if i have to split on several columns (like filtering) then that would not be practical. i mean, the thrust of these "lookups" are through appearance of one-line code, quick, and dirty, and not complicated and not time consuming. they don't want the new person spending time "thinking" about anything. just click, drag, search, done. but i'm afraid that what i'm looking to do is not possible. otherwise, we may have to let the new person go. i have another few days to figure this out. thank you for the tips.

edit 1: ok, i got the MATCH figured out. so i know how to find the position or row in the column of a search.

[vhelp]

- solved -

finally figured it out, the custom lookup search, and it works. so, for what i was trying to do, vlookup was not the right method.

....thank you for the tips, after trial and error, it helped me figure out what i needed to do, and i don't have to let the person go!